package com.xyh.dao.impl;

import com.xyh.bean.Insect;
import com.xyh.dao.IInsectDao;
import com.xyh.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: XiongYuhao
 * @Date: 2020/10/23 0023-上午 10:42
 * @Description: MyForestManagerSystem
 * @version：1.0
 */
public class InsectDaoImpl implements IInsectDao {
    /**
     * 获取昆虫数量
     *
     * @param insectName
     * @param host
     * @return
     */
    public int insectCounts(String insectName, String host) {
        int counts = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            String sql = "select count(0) from t_insect where 1=1 ";
            if (insectName != null && !insectName.equalsIgnoreCase("")) {
                sql += " and f_insect like '%" + insectName + "%'";
            }
            if (host != null && !host.equalsIgnoreCase("")) {
                sql += " and f_host like '%" + host + "%'";
            }
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                counts = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return counts;
    }

    /**
     * 虫害一览并分页
     *
     * @param pageNo
     * @param pageSize
     * @return
     */
    public List<Insect> listsByPage(int pageNo, int pageSize, String insectName, String host) {
        List<Insect> insects = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            StringBuffer sql = new StringBuffer("select * from t_insect where 1=1 ");
            if (insectName != null && !insectName.equalsIgnoreCase("")) {
                sql.append(" and f_insect like '%" + insectName + "%'");
            }
            if (host != null && !host.equalsIgnoreCase("")) {
                sql.append(" and f_host like '%" + host + "%'");
            }
            sql.append(" limit " + (pageNo - 1) * pageSize + "," + pageSize);

            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Insect insect = new Insect();
                insect.setInsectId(rs.getInt("pk_id"));
                insect.setInsectName(rs.getString("f_insect"));
                insect.setHost(rs.getString("f_host"));
                insect.setDanger(rs.getString("f_danger"));
                insect.setBreed(rs.getString("f_breed"));
                insect.setEnemy(rs.getString("f_enemy"));
                insect.setMeasures(rs.getString("f_measures"));
                insect.setLarvaPictures(rs.getString("f_larvapictures"));
                insect.setAdultPictures(rs.getString("f_adultpictures"));
                insects.add(insect);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return insects;
    }

    /**
     * 添加虫害
     *
     * @param insect
     * @return
     */
    public int addInsect(Insect insect) {
        int line = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConn();
            String sql = "insert into t_insect (f_insect,f_host,f_danger,f_breed,f_enemy,f_measures,f_larvapictures,f_adultpictures) values (?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,insect.getInsectName());
            ps.setString(2,insect.getHost());
            ps.setString(3,insect.getDanger());
            ps.setString(4,insect.getBreed());
            ps.setString(5,insect.getEnemy());
            ps.setString(6,insect.getMeasures());
            ps.setString(7,insect.getLarvaPictures());
            ps.setString(8,insect.getAdultPictures());
            line = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(null, ps, conn);
        }
        return line;
    }

    @Override
    public int delete(int id) {
        int line = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConn();
            String sql = "delete from t_insect where pk_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            line = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(null, ps, conn);
        }
        return line;
    }

    @Override
    public int update(Insect insect) {
        int line = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConn();
            String sql = "update t_insect set f_insect=?,f_host=?,f_danger=?,f_breed=?,f_enemy=?,f_measures=?,f_larvapictures=?,f_adultpictures=? where pk_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,insect.getInsectName());
            ps.setString(2,insect.getHost());
            ps.setString(3,insect.getDanger());
            ps.setString(4,insect.getBreed());
            ps.setString(5,insect.getEnemy());
            ps.setString(6,insect.getMeasures());
            ps.setString(7,insect.getLarvaPictures());
            ps.setString(8,insect.getAdultPictures());
            ps.setInt(9,insect.getInsectId());
            line = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(null, ps, conn);
        }
        return line;
    }
}
